using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Data.SqlClient;

using SIS.ENT;

/// <summary>
/// The TeacherJobExperiencesTableDB class is responsible for interacting with the database to retrieve and store information
/// about TeacherJobExperiencesTable objects.
/// </summary>
public class TeacherJobExperiencesTableDB
{
    #region "Public Methods"

    /// <summary>
    /// Gets an instance of TeacherJobExperiencesTable from the underlying datasource.
    /// </summary>
    /// <param name="teacherJobExperienceKey">The unique TeacherJobExperienceKey of the TeacherJobExperiencesTable in the database.</param>
    /// <returns>An TeacherJobExperiencesTable when the TeacherJobExperienceKey was found in the database, or null otherwise.</returns>
    public static TeacherJobExperiencesTable GetItem(int teacherJobExperienceKey)
    {
        TeacherJobExperiencesTable myTeacherJobExperiencesTable = null;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherJobExperiencesTableSelectSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@teacherJobExperienceKey", teacherJobExperienceKey);

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.Read())
                    {
                        myTeacherJobExperiencesTable = FillDataRecord(myReader);
                    }
                    myReader.Close();
                }
                myConnection.Close();
            }
            return myTeacherJobExperiencesTable;
        }
    }

    /// <summary>
    /// Returns a list with TeacherJobExperiencesTable objects.
    /// </summary>
    /// <returns>A generics List with the TeacherJobExperiencesTable objects.</returns>
    public static TeacherJobExperiencesTableList GetList()
    {
        TeacherJobExperiencesTableList tempList = new TeacherJobExperiencesTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherJobExperiencesTableSelectList", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
        }
        return tempList;
    }

    public static TeacherJobExperiencesTableList GetList(int UserAccountKey)
    {
        TeacherJobExperiencesTableList tempList = new TeacherJobExperiencesTableList();
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherJobExperiencesTableSelectList", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@UserAccountKey", UserAccountKey);
                myConnection.Open();
                using (SqlDataReader myReader = myCommand.ExecuteReader())
                {
                    if (myReader.HasRows)
                    {
                        while (myReader.Read())
                        {
                            tempList.Add(FillDataRecord(myReader));
                        }
                    }
                    myReader.Close();
                }
            }
        }
        return tempList;
    }

    /// <summary>
    /// Saves a TeacherJobExperiencesTable in the database.
    /// </summary>
    /// <param name="myTeacherJobExperiencesTable">The TeacherJobExperiencesTable instance to save.</param>
    /// <returns>The new TeacherJobExperienceKey if the TeacherJobExperiencesTable is new in the database or the existing TeacherJobExperienceKey when an item was updated.</returns>
    public static int Save(TeacherJobExperiencesTable myTeacherJobExperiencesTable)
    {
        int result = 0;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherJobExperiencesTableInsertUpdateSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                if (myTeacherJobExperiencesTable.TeacherJobExperienceKey == -1)
                {
                    myCommand.Parameters.AddWithValue("@teacherJobExperienceKey", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@teacherJobExperienceKey", myTeacherJobExperiencesTable.TeacherJobExperienceKey);
                }
                if (myTeacherJobExperiencesTable.UserAccountKey == null)
                {
                    myCommand.Parameters.AddWithValue("@UserAccountKey", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@UserAccountKey", myTeacherJobExperiencesTable.UserAccountKey);
                }
                if (string.IsNullOrEmpty(myTeacherJobExperiencesTable.PartnerSchool))
                {
                    myCommand.Parameters.AddWithValue("@partnerSchool", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@partnerSchool", myTeacherJobExperiencesTable.PartnerSchool);
                }
                if (myTeacherJobExperiencesTable.StartDate == null)
                {
                    myCommand.Parameters.AddWithValue("@startDate", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@startDate", myTeacherJobExperiencesTable.StartDate);
                }
                if (myTeacherJobExperiencesTable.EndDate == null)
                {
                    myCommand.Parameters.AddWithValue("@endDate", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@endDate", myTeacherJobExperiencesTable.EndDate);
                }
                if (string.IsNullOrEmpty(myTeacherJobExperiencesTable.LevelTaught))
                {
                    myCommand.Parameters.AddWithValue("@levelTaught", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@levelTaught", myTeacherJobExperiencesTable.LevelTaught);
                }
                if (myTeacherJobExperiencesTable.BasicSalary == null)
                {
                    myCommand.Parameters.AddWithValue("@basicSalary", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@basicSalary", myTeacherJobExperiencesTable.BasicSalary);
                }
                if (myTeacherJobExperiencesTable.RefereeName == null)
                {
                    myCommand.Parameters.AddWithValue("@refereeName", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@refereeName", myTeacherJobExperiencesTable.RefereeName);
                }
                if (myTeacherJobExperiencesTable.RefereeAddress == null)
                {
                    myCommand.Parameters.AddWithValue("@refereeAddress", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@refereeAddress", myTeacherJobExperiencesTable.RefereeAddress);
                }
                if (myTeacherJobExperiencesTable.RefereeEmail == null)
                {
                    myCommand.Parameters.AddWithValue("@refereeEmail", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@refereeEmail", myTeacherJobExperiencesTable.RefereeEmail);
                }
                if (myTeacherJobExperiencesTable.RefereeContact == null)
                {
                    myCommand.Parameters.AddWithValue("@refereeContact", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@refereeContact", myTeacherJobExperiencesTable.RefereeContact);
                }
                if (string.IsNullOrEmpty(myTeacherJobExperiencesTable.Remarks))
                {
                    myCommand.Parameters.AddWithValue("@remarks", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@remarks", myTeacherJobExperiencesTable.Remarks);
                }
                if (myTeacherJobExperiencesTable.Status == null)
                {
                    myCommand.Parameters.AddWithValue("@status", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@status", myTeacherJobExperiencesTable.Status);
                }
                if (myTeacherJobExperiencesTable.CreatedDateTime == null)
                {
                    myCommand.Parameters.AddWithValue("@createdDateTime", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@createdDateTime", myTeacherJobExperiencesTable.CreatedDateTime);
                }
                if (myTeacherJobExperiencesTable.ModifiedDateTime == null)
                {
                    myCommand.Parameters.AddWithValue("@modifiedDateTime", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@modifiedDateTime", myTeacherJobExperiencesTable.ModifiedDateTime);
                }

                DbParameter returnValue;
                returnValue = myCommand.CreateParameter();
                returnValue.Direction = ParameterDirection.ReturnValue;
                myCommand.Parameters.Add(returnValue);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                result = Convert.ToInt32(returnValue.Value);
                myConnection.Close();
            }
        }
        return result;
    }

    /// <summary>
    /// Deletes a TeacherJobExperiencesTable from the database.
    /// </summary>
    /// <param name="teacherJobExperienceKey">The TeacherJobExperienceKey of the TeacherJobExperiencesTable to delete.</param>
    /// <returns>Returns true when the object was deleted successfully, or false otherwise.</returns>
    public static bool Delete(int teacherJobExperienceKey)
    {
        int result = 0;
        using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["sis.dbconn"].ConnectionString))
        {
            using (SqlCommand myCommand = new SqlCommand("sprocTeacherJobExperiencesTableDeleteSingleItem", myConnection))
            {
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.AddWithValue("@teacherJobExperienceKey", teacherJobExperienceKey);
                myConnection.Open();
                result = myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }
        return result > 0;
    }
    #endregion

    /// <summary>
    /// Initializes a new instance of the TeacherJobExperiencesTable class and fills it with the data fom the IDataRecord.
    /// </summary>
    /// 
    private static TeacherJobExperiencesTable FillDataRecord(IDataRecord myDataRecord)
    {
        TeacherJobExperiencesTable myTeacherJobExperiencesTable = new TeacherJobExperiencesTable();
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("TeacherJobExperienceKey")))
        {
            myTeacherJobExperiencesTable.TeacherJobExperienceKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("TeacherJobExperienceKey"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("UserAccountKey")))
        {
            myTeacherJobExperiencesTable.UserAccountKey = myDataRecord.GetInt32(myDataRecord.GetOrdinal("UserAccountKey"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("PartnerSchool")))
        {
            myTeacherJobExperiencesTable.PartnerSchool = myDataRecord.GetString(myDataRecord.GetOrdinal("PartnerSchool"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("StartDate")))
        {
            myTeacherJobExperiencesTable.StartDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("StartDate"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("EndDate")))
        {
            myTeacherJobExperiencesTable.EndDate = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("EndDate"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("LevelTaught")))
        {
            myTeacherJobExperiencesTable.LevelTaught = myDataRecord.GetString(myDataRecord.GetOrdinal("LevelTaught"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("BasicSalary")))
        {
            myTeacherJobExperiencesTable.BasicSalary = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("BasicSalary"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RefereeName")))
        {
            myTeacherJobExperiencesTable.RefereeName = myDataRecord.GetString(myDataRecord.GetOrdinal("RefereeName"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RefereeAddress")))
        {
            myTeacherJobExperiencesTable.RefereeAddress = myDataRecord.GetString(myDataRecord.GetOrdinal("RefereeAddress"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RefereeEmail")))
        {
            myTeacherJobExperiencesTable.RefereeEmail = myDataRecord.GetString(myDataRecord.GetOrdinal("RefereeEmail"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("RefereeContact")))
        {
            myTeacherJobExperiencesTable.RefereeContact = myDataRecord.GetString(myDataRecord.GetOrdinal("RefereeContact"));
        }

        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Remarks")))
        {
            myTeacherJobExperiencesTable.Remarks = myDataRecord.GetString(myDataRecord.GetOrdinal("Remarks"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("Status")))
        {
            myTeacherJobExperiencesTable.Status = myDataRecord.GetInt16(myDataRecord.GetOrdinal("Status"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("CreatedDateTime")))
        {
            myTeacherJobExperiencesTable.CreatedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("CreatedDateTime"));
        }
        if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("ModifiedDateTime")))
        {
            myTeacherJobExperiencesTable.ModifiedDateTime = myDataRecord.GetDateTime(myDataRecord.GetOrdinal("ModifiedDateTime"));
        }
        return myTeacherJobExperiencesTable;
    }

}
